﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessInstockDetailed
// 文件功能描述：食材明细数据访问层
//
// 
// 创建标识：2012-01-14 zhou
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessInstockDetailed
    {
        /// <summary>
        /// 保存食材明细
        /// </summary>
        /// <param name="idpara"></param>
        /// <returns></returns>
        public int SaveInstockDetailed(object[] idpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_ConInstockDetailed", idpara);
        }

        /// <summary>
        /// 食材明细总数
        /// </summary>
        /// <returns></returns>
        public int GetCountInstockDetailed(string condition)
        {
            string query = "select count(1) from Con_Instock_Detailed where 1=1";
            if (!string.IsNullOrEmpty(condition))
            {
                query += condition;
            }
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 获取食材明细不分页
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet GetAllInstockDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * FROM Con_Instock_Detailed Where 1=1 " + condition;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 获取食材明细分页
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pagesize"></param>
        /// <param name="startindex"></param>
        /// <returns></returns>
        public DataSet GetAllInstockDetailed(string condition, int pagesize, int startindex)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "SELECT B.ID,B.InstockDt,B.Warehouse_ID,B.Group_ID,"
                    + "B.Goods_ID,B.Count,B.Price,B.Amount,B.Supplier_ID,B.RecordDateTime,B.OperatorID,OrderNo "
                    + "FROM (select ROW_NUMBER() OVER(ORDER BY A.InstockDt DESC,A.Warehouse_ID,A.Group_ID) rownumber,ID,InstockDt,Warehouse_ID,Group_ID,"
                    + "Goods_ID,Count,Price,Amount,Supplier_ID,RecordDateTime,OperatorID,OrderNo "
                    + "from Con_Instock_Detailed A Where 1=1 " + condition + ") B "
                    + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteInstockDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "Delete from Con_Instock_Detailed where " + condition;
                return db.ExecuteNonQuery(conn, CommandType.Text, query);
            }
        }

        /// <summary>
        /// 根据条件删除(王冲)
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteInstockDetailedWC(string condition, int id)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                /*
                 *先根据主副调食材入库编号查询出食材类型编号和单价，然后再去主副调食材出库表里面查询一下有没有这个食材类型
                 *编号和这个单价的出库信息，如果有则不能删除直接返回0，如果没有则可以删除，返回删除影响行数。
                 */
                int i;
                sqldb db = new sqldb();
                string query = "select Goods_ID,Price from Con_Instock_Detailed where ID='" + id + "'";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                query = "select count(*) from Con_Outstock_Detailed where Goods_Id='" + ds.Tables[0].Rows[0]["Goods_ID"].ToString() + "' and Price='" + ds.Tables[0].Rows[0]["Price"].ToString() + "'";
                ds = db.ExecuteDataset(conn, CommandType.Text, query);
                if (ds.Tables[0].Rows[0][0].ToString() == "0")
                {
                    query = "Delete from Con_Instock_Detailed where " + condition;
                    i = db.ExecuteNonQuery(conn, CommandType.Text, query);
                }
                else
                {
                    i = 0;
                }
                return i;
            }
        }

        /// <summary>
        /// 获取批号
        /// </summary>
        /// <returns></returns>
        public string GetInstockDetailedOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetCon_Instock_Detailed_OrderNo";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }
    }
}
